--USE MovieMgr;

IF EXISTS (SELECT 1 FROM sysobjects where name='MuveeDetailsView' and type='v')
DROP VIEW MuveeDetailsView
GO

--SELECT * FROM MuveeDetailsView
CREATE VIEW MuveeDetailsView
AS
SELECT MuveeMain.PKey
	,MuveeMain.StagePKey
	,MuveeMain.SerialNo
	,MuveeMain.tmpLoaned
	,MuveeMain.Title
	,MuveeMain.AlternateTitle
	,MuveeMain.IMDbCode as "ID"
	,case(MuveeMain.tmpSeen) when 1 then 'Y' else 'N' end as "tmpSeen"
	,2*COALESCE(Watchlist.DateAdded,0)-COALESCE(Watchlist.DateWatched,0) as "Seen"
	,MuveeMain.IMDbRating as "Rating"
	,MuveeMain.CustomRating as "MyRating"
	,MuveeMain.tmpDirector as "tmpDirector"
	,COALESCE(DirectorMain.Name,'') as "Director"
	,COALESCE(VolumeMain.Volume,'') AS Volume
	,MuveeMain.tmpGenre as "Genre"
	,MuveeMain.ReleaseYear as "Year"
	,MuveeMain.Studio
	,COALESCE(CollectionMain.Collection,'') AS Collection	
--	,MuveeMain.Casting
	,CastingMain.Casting
	,MuveeMain.Plot
	,MuveeMain.Notes
	,MuveeMain.tmpCountry as "Country"
	,MuveeMain.Poster as "Poster"
	,MuveeMain.PosterLocation as "PosterLocation"
	,MuveeMain.Classification
	,MuveeMain.nDiscs
	,MuveeMain.RunTimeMins as "RunTime"
	,COALESCE(LayerMain.Layer,'') AS Layer
	,COALESCE(ColourMain.Colour,'') AS Colour
	,COALESCE(RegionMain.Region,'') AS Region
	,COALESCE(MediumMain.Medium,'') AS Medium
	,COALESCE(VCodecMain.VCodec,'') AS VCodec
	,'' AS ACodec --,COALESCE(ACodecMain.ACodec,'--') AS ACodec
	,COALESCE(CondMain.Condition,'') AS Condition
	,COALESCE(MuveeMain.IMDbLink,'') AS IMDbLink
	,COALESCE(MuveeMain.IMDbTrailerLink,'') AS IMDbTrailerLink
	,COALESCE(MuveeMain.OfficialSite,'') AS OfficialSite
	,COALESCE(MuveeMain.WikipediaLink,'') AS WikipediaLink
	,MuveeMain.MyReviews
	,MuveeMain.Status
	
	,MuveeMain.VolumeMainPKey
	,MuveeMain.CollectionMainPKey
	,MuveeMain.LayerMainPKey
	,MuveeMain.ColourMainPKey
	,MuveeMain.RegionMainPKey
	,MuveeMain.MediumMainPKey
	,MuveeMain.VCodecMainPKey
	,0 as ACodecMainPKey --MuveeMain.ACodecMainPKey
	,MuveeMain.CondMainPKey
	,RelDirector.PKey as RelDirectorPKey

FROM EntertainmentMain MuveeMain
LEFT OUTER JOIN MuveeDirectorRel RelDirector LEFT OUTER JOIN PeopleMain DirectorMain ON DirectorMain.PKey=RelDirector.PeopleMainPKey AND DirectorMain.Status<>'d' AND RelDirector.Status<>'d' ON MuveeMain.PKey=RelDirector.MuveeMainPKey AND RelDirector.Status<>'d'
LEFT OUTER JOIN VolumeMain ON VolumeMain.PKey=MuveeMain.VolumeMainPKey and VolumeMain.Status<>'d' and MuveeMain.Status<>'d'
LEFT OUTER JOIN CollectionMain ON CollectionMain.PKey=MuveeMain.CollectionMainPKey and CollectionMain.Status<>'d' and MuveeMain.Status<>'d'
LEFT OUTER JOIN LayerMain ON LayerMain.PKey=MuveeMain.LayerMainPKey and LayerMain.Status<>'d' and MuveeMain.Status<>'d'
LEFT OUTER JOIN ColourMain ON ColourMain.PKey=MuveeMain.ColourMainPKey and ColourMain.Status<>'d' and MuveeMain.Status<>'d'
LEFT OUTER JOIN RegionMain ON RegionMain.PKey=MuveeMain.RegionMainPKey and RegionMain.Status<>'d' and MuveeMain.Status<>'d'
LEFT OUTER JOIN MediumMain ON MediumMain.PKey=MuveeMain.MediumMainPKey and MediumMain.Status<>'d' and MuveeMain.Status<>'d'
LEFT OUTER JOIN VCodecMain ON MediumMain.PKey=MuveeMain.VCodecMainPKey and VCodecMain.Status<>'d' and MuveeMain.Status<>'d'
--LEFT OUTER JOIN ACodecMain ON MediumMain.PKey=MuveeMain.ACodecMainPKey and ACodecMain.Status<>'d' and MuveeMain.Status<>'d'
LEFT OUTER JOIN CondMain ON MediumMain.PKey=MuveeMain.CondMainPKey and CondMain.Status<>'d' and MuveeMain.Status<>'d'
LEFT OUTER JOIN CastingMain ON CastingMain.EntertainmentMainPKey=MuveeMain.PKey and CastingMain.Status<>'d' and MuveeMain.Status<>'d'
LEFT OUTER JOIN
(
	SELECT w1.EntertainmentMainPKey,
		CASE WHEN DateAdded IS NOT NULL THEN 1 ELSE 0 END as DateAdded,
		CASE WHEN DateWatched IS NOT NULL THEN 1 ELSE 0 END as DateWatched
	FROM WatchlistMain w1
	WHERE Status<>'d'
	and w1.PKey IN
	(
		SELECT TOP 1 PKey
		FROM WatchlistMain w2
		WHERE Status<>'d'
		and w1.EntertainmentMainPKey=w2.EntertainmentMainPKey
		ORDER BY PKey DESC
	)
) Watchlist ON Watchlist.EntertainmentMainPKey=MuveeMain.PKey and MuveeMain.Status<>'d'

WHERE MuveeMain.Status<>'d'
	and MuveeMain.SeriesParentPKey=''
